package com.mobilesales.android.database;


import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.util.Log;

import com.mobilesales.android.model.clients.Client;
import com.mobilesales.android.model.clients.ClientGroup;
import com.mobilesales.android.model.dictionary.Dictionary;
import com.mobilesales.android.model.dictionary.KeyValuePair;
import com.mobilesales.android.model.params.Param;

public class ClientManager extends ManagerBase{
	
	public ClientManager(Context c)
	{
		super(c);
	}
	
	public List<Client> GetClientList()
	{
     	try {  		
     		String query = "SELECT _id"
     					+ " FROM Clients";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<Client> result = new ArrayList<Client>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetClient(id));
     				
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public Client GetClient(int clientId)
	{
     	try { 		
     		String query = "SELECT _id, ClientGroupId, Name, Name2, Nip, Zipcode, City, Street, Phone, Fax, Info, Info2, Debts, IsSupplier, LastVisitTime" 
							+ " FROM Clients" 
							+ " WHERE _id = " + clientId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Client result = new Client();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		int clientgroupID = c.getInt(c.getColumnIndex("ClientGroupId"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Name2 = c.getString(c.getColumnIndex("Name2"));
     		result.Nip = c.getString(c.getColumnIndex("Nip"));
     		result.Zipcode = c.getString(c.getColumnIndex("Zipcode"));
     		result.City = c.getString(c.getColumnIndex("City"));
     		result.Street = c.getString(c.getColumnIndex("Street"));
     		result.Phone = c.getString(c.getColumnIndex("Phone"));
     		result.Fax = c.getString(c.getColumnIndex("Fax"));
     		result.Info = c.getString(c.getColumnIndex("Info"));
     		result.Info2 = c.getString(c.getColumnIndex("Info2"));
     		result.Debts = c.getDouble(c.getColumnIndex("Debts"));
     		result.IsSupplier = c.getInt(c.getColumnIndex("IsSupplier")) == 1 ? true : false;
     		result.LastVisitTime = c.getString(c.getColumnIndex("LastVisitTime"));

     		c.close();
     		result.Group = GetClientGroup(clientgroupID);
     		result.Params = GetClientParams(result.Id);
     		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public boolean StoreClient(Client client)
	{
     	try {
			ContentValues cv = new ContentValues();

			if (client.Group != null)
				cv.put("ClientGroupId", client.Group.Id);
			else
				cv.putNull("ClientGroupId");
			cv.put("_id", client.Id);
			cv.put("Name",client.Name);
			cv.put("Name2", client.Name2);
			cv.put("Nip", client.Nip);
			cv.put("Zipcode", client.Zipcode);
			cv.put("City", client.City);
			cv.put("Street", client.Street);
			cv.put("Phone", client.Phone);
			cv.put("Fax", client.Fax);
			cv.put("Info", client.Info);
			cv.put("Info2", client.Info2);
			cv.put("Debts", client.Debts);
			cv.put("IsSupplier", client.IsSupplier);
			cv.put("LastVisitTime", client.LastVisitTime);
			
			int clientID;
			int result = 1;

			result = db.update("Clients", cv, "_id ="+client.Id, null);
			
			
			if (result == 0)
				clientID = (int)db.insert("Clients", null, cv);
			else
				clientID = client.Id;	

			if (clientID == -1)
				return false;
			
			result = StoreClientParams(clientID, client.Params) == true ? 1 : 0;
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveClient(int clientId)
	{
     	try {
     		int result;
			result = db.delete("Clients", "_id ="+clientId, null);
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public Dictionary<Param, String> GetClientParams(int clientId)
	{
     	try {  		
     		String query = "SELECT ParamId, Value"
	    			+ " FROM ClientHasParams" 
	    			+ " WHERE ClientId = " + clientId;
     		Cursor c = db.rawQuery(query, null);
     		
     		Dictionary<Param, String> result = new Dictionary<Param,String>();
    		if (!c.moveToFirst())
    			return result;
    		ParamManager pm = new ParamManager(this.context);
    		Dictionary<Integer, String> tmp = new Dictionary<Integer, String>();
	     	while (!c.isAfterLast()){    	     		
		     	KeyValuePair<Integer, String> p = new KeyValuePair<Integer, String>();
		     	p.setKey(c.getInt(c.getColumnIndex("ParamId")));
		     	p.setValue(c.getString(c.getColumnIndex("Value")));
		        tmp.add(p);
		        c.moveToNext();
	     	}
	     	
	     	c.close();
	     	for (KeyValuePair<Integer, String> kpv : tmp){
		     	KeyValuePair<Param, String> p = new KeyValuePair<Param, String>();
		     	p.setKey(pm.GetParam(kpv.getKey()));
		     	p.setValue(kpv.getValue());
		        result.add(p);
	     	}
	      		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;		
	}
	
	public boolean StoreClientParams(int clientId, Dictionary<Param,String> paramMap)
	{
		if (paramMap == null) return false;
     	try {
     		int result = 1;
     		String query = "SELECT ParamId"
    			+ " FROM ClientHasParams" 
    			+ " WHERE ClientId = " + clientId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> paramsId = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (!c.isAfterLast()){    	     		
	         		paramsId.add(c.getInt(c.getColumnIndex("ParamId")));
	         		c.moveToNext();
	     		}
	     	}
    		c.close();
	 		List<Integer> addedParams = new ArrayList<Integer>();
	 		for (KeyValuePair<Param, String> kpv : paramMap){
	 			result = 1;
	 			boolean exists = paramsId.contains(kpv.getKey().Id);
	 			
				ContentValues cv = new ContentValues();
				
				boolean hasValue = kpv.getValue() != null;

				cv.put("ClientId", clientId);
				cv.put("ParamId",kpv.getKey().Id);
				cv.put("Value", kpv.getValue());
	
				if(hasValue) {
		 			addedParams.add(kpv.getKey().Id);
					if (exists)
						result = db.update("ClientHasParams", cv, "ClientId ="+clientId + " and ParamId =" + kpv.getKey().Id, null);
					else
						result = (int)db.insert("ClientHasParams", null, cv);
				}
				
				if (result <= 0)
					throw new Exception ("Failed to update/insert client params " + result);
	 		}
	 		
	 		for (int paramId : paramsId)
	 		{
	 			if (!addedParams.contains(paramId))
		 	     	try {
		 				result = db.delete("ClientHasParams", "ClientId ="+clientId + " and ParamId =" + paramId, null);
		 				if (result <= 0)
		 					throw new Exception("Failed to remove param row from ClientHasParams. ClientId" + clientId + " ParamId " + paramId);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
	 		return result > 0 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}		
	}
	
	public List<ClientGroup> GetClientGroupList()
	{
     	try {
     		
     		String query = "SELECT _id"
     						+ " FROM ClientGroups";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<ClientGroup> result = new ArrayList<ClientGroup>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetClientGroup(id));
     				
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	
	public ClientGroup GetClientGroup(int clientGroupId)
	{
     	try {
     		
     		String query = "SELECT _id, Name, Discount" 
							+ " FROM ClientGroups" 
							+ " WHERE _id = " + clientGroupId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		ClientGroup result = new ClientGroup();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Discount = c.getDouble(c.getColumnIndex("Discount"));
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	public boolean StoreClientGroup(ClientGroup clientGroup)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", clientGroup.Id);
			cv.put("Name",clientGroup.Name);
			cv.put("Discount", clientGroup.Discount);
			
			result = db.update("ClientGroups", cv, "_id ="+clientGroup.Id, null);
			if (result == 0)
				result = db.insert("ClientGroups", null, cv) != -1 ? 1 : 0;
			
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveClientGroup(int clientGroupId)
	{
     	try {
     		int result;
			result = db.delete("ClientGroups", "_id ="+clientGroupId, null);
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
}
